package persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import persistence.DBSession;

/**
 * @author: Humberto Rocha Loureiro (humbertorocha@gmail.com)
 * @modify: 
 */

public class PerformanceEngineDAO { 

	private Connection conn = null;
	private ResultSet rs = null;
	private Statement stat = null;
	
	
	public void update(int id, int tipo, String date, String psell){
		try {
			conn = DBSession.getIstance().getConnection();
			PreparedStatement pstat = null;
			String field = "";
			field = (tipo==2) ? " , datebuy = ? , psell = ? " : "  ,datesell = ? , psell = ? ";
			String sql = " update performance set  tipo = ? " + field + " where id = ? ";

			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, tipo);
			pstat.setString(2, date);
			pstat.setString(3, psell);
			pstat.setInt(4, id);
			
			pstat.execute();
			pstat.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void delete(int id){
		try {
			conn = DBSession.getIstance().getConnection();
			PreparedStatement pstat = null;
			
			String sql = " delete from performance where id = ? ";

			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, id);
			
			pstat.execute();
			pstat.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void persist(PerformanceVO vo){
		try {
			conn = DBSession.getIstance().getConnection();
			PreparedStatement pstat = null;
			
			String sql = " insert into performance (date, stock, buysell, book, pbuy, pstoploss, plimit, datelimit) values (?,?,?,?,?,?,?,?)";

			pstat = conn.prepareStatement(sql);
			pstat.setString(1, vo.getDate());
			pstat.setString(2, vo.getStock());
			pstat.setInt(3, vo.getBuysell());
			pstat.setString(4, vo.getBook());
			pstat.setString(5, vo.getPbuy());
			pstat.setString(6, vo.getPstoploss());
			pstat.setString(7, vo.getPlimit());
			pstat.setString(8, vo.getDatelimit()); 
			
			pstat.execute();
			pstat.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public List<PerformanceVO> listPerformance(int type, String date) {
		List<PerformanceVO> result = new ArrayList<PerformanceVO>();
		conn = DBSession.getIstance().getConnection();
		PreparedStatement pstat = null;
		try {

			String sql = " SELECT id, date, datebuy, datesell, stock, buysell, book, pbuy, pstoploss, plimit, tipo, datelimit, psell " +
						 "  from performance " +
						 "  where " +
						 "  date <= ? and tipo = ?";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, date);
			pstat.setInt(2, type);

			rs = pstat.executeQuery();
			while(rs.next()) {
				PerformanceVO vo = new PerformanceVO();
				vo.setId(rs.getString("id"));
				vo.setDate(rs.getString("date"));
				vo.setDateBuy(rs.getString("datebuy"));
				vo.setDateSell(rs.getString("datesell"));
				vo.setStock(rs.getString("stock"));
				vo.setBuysell(rs.getInt("buysell"));
				vo.setBook(rs.getString("book"));
				vo.setPbuy(rs.getString("pbuy"));
				vo.setPsell(rs.getString("psell"));
				vo.setPstoploss(rs.getString("pstoploss"));
				vo.setPlimit(rs.getString("plimit"));
				QuoteVO quoteVO = getLastPrice(rs.getString("stock"), (type==3)? rs.getString("datesell") : date);
				vo.setPriceCloseCurr(quoteVO.getClose());
				vo.setPriceLowCurr(quoteVO.getLow());
				vo.setPriceHighCurr(quoteVO.getHigh());
				vo.setType(rs.getInt("tipo"));
				vo.setDatelimit(rs.getString("datelimit"));
				AnalyzedVO analyzedVO = getLastAlert(rs.getString("stock"), date);
				vo.setIdScript(analyzedVO.getIdscript());
				vo.setLastAlertIdScript(analyzedVO.getDate());
				result.add(vo);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				if(rs!=null)
					rs.close();
				if(pstat!=null)
					pstat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	public QuoteVO getLastPrice(String stock, String date) {
		String result = "";
		conn = DBSession.getIstance().getConnection();
		ResultSet rs = null;
		PreparedStatement pstat = null;
		QuoteVO vo = new QuoteVO();
		try {
			
			String sql = "  select close, low, high " + 
						 "  from quote " +
						 "  where stock = ? and date <= ?" +
						 "  order by date desc limit 0,1 ";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, stock);
			pstat.setString(2, date);
		
			rs = pstat.executeQuery();
			if(rs.next()) {
				vo.setClose(rs.getString("close"));
				vo.setLow(rs.getString("low"));
				vo.setHigh(rs.getString("high"));
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				if(rs!=null)
					rs.close();
				if(pstat!=null)
					pstat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return vo;
	}
	
	public AnalyzedVO getLastAlert(String stock, String date) {
		String result = "";
		conn = DBSession.getIstance().getConnection();
		ResultSet rs = null;
		PreparedStatement pstat = null;
		AnalyzedVO vo = new AnalyzedVO();
		try {
			
			String sql = "  select idscript, date " + 
						 "  from analyzed " +
						 "  where idstock = ? " +
						 "  and date <= ? " + 
						 "	and ((idscript = 145) or (idscript = 146) or (idscript = 147)) " +
						 "  order by date desc limit 0,1 ";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, stock);
			pstat.setString(2, date);
		
			rs = pstat.executeQuery();
			if(rs.next()) {
				vo.setIdscript(rs.getString("idscript"));
				vo.setDate(rs.getString("date"));
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				if(rs!=null)
					rs.close();
				if(pstat!=null)
					pstat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return vo;
	}	
}
